﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;

namespace DotNetMigrations
{
	public class DAL
	{
		public static string ConnectionString { get; set; }

		public static string GetProvider()
		{
			DbConnectionStringBuilder csb = new DbConnectionStringBuilder();
			csb.ConnectionString = ConnectionString;
			string provider = "System.Data.SqlClient";	// default factory provider
			if (csb.ContainsKey("provider"))
				provider = csb["provider"].ToString();
			
			return provider;
		}

		private static DbProviderFactory GetFactory()
		{
			return DbProviderFactories.GetFactory(GetProvider());
		}

		public static object ExecuteScalar(string sql)
		{
			return ExecuteScalar(sql, false);
		}

		public static object ExecuteScalar(string sql, bool suppressErrors)
		{
			DbConnection conn = GetConnection();
			if (conn == null)
				return null;

			try
			{
				DbCommand comm = conn.CreateCommand();
				comm.CommandText = sql;
				comm.CommandType = System.Data.CommandType.Text;
				conn.Open();
				object obj = comm.ExecuteScalar();
				return obj;
			}
			catch (Exception ex)
			{
				if (!suppressErrors)
					Output.Warn(ex.Message);
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}

			return null;
		}

		public static void ExecuteNonQuery(string sql)
		{
			DbConnection conn = GetConnection();
			if (conn == null)
				return;

			try
			{
				DbCommand comm = conn.CreateCommand();
				comm.CommandText = sql;
				comm.CommandType = System.Data.CommandType.Text;
				conn.Open();
				comm.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				Output.Warn(ex.Message);
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}
		}

		public static DbCommand GetCommand()
		{
			return GetFactory().CreateCommand();
		}

		public static DbParameter GetParameter(string name, object value)
		{
			DbParameter p = GetFactory().CreateParameter();
			p.ParameterName = name;
			p.Value = value;
			return p;
		}

		public static bool ExecuteCommands(List<DbCommand> commands)
		{
			DbConnection conn = GetConnection();
			if (conn == null)
				return false;

			DbTransaction tran = null;
			try
			{
				conn.Open();
				tran = conn.BeginTransaction();
				foreach (DbCommand comm in commands)
				{
					comm.Connection = conn;
					comm.CommandType = System.Data.CommandType.Text;
					comm.Transaction = tran;
					comm.ExecuteNonQuery();
				}

				tran.Commit();
				return true;
			}
			catch (Exception ex)
			{
				if (tran != null)
					tran.Rollback();
				Output.Warn(ex.Message);
				return false;
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}
		}

		public static bool ExecuteScripts(List<string> scripts)
		{
			List<DbCommand> commands = new List<DbCommand>();

			foreach (string sql in scripts)
			{
				//	make sure the SQL isn't null or an empty string
				if (sql == null || sql.Trim().Length == 0)
					continue;

				DbCommand comm = GetCommand();
				comm.CommandText = sql;
				commands.Add(comm);
			}

			return ExecuteCommands(commands);
		}

		private static DbConnection GetConnection()
		{
			try
			{
				DbConnection conn = GetFactory().CreateConnection();
				DbConnectionStringBuilder csb = new DbConnectionStringBuilder();
				csb.ConnectionString = ConnectionString;
				csb.Remove("provider");
				conn.ConnectionString = csb.ToString();
				return conn;
			}
			catch (Exception ex)
			{
				Output.Warn("Failed to create connection: " + ex.Message);
				return null;
			}
		}
	}
}
